[小ネタ]Snowflakeで全体を[]で囲われたJSONファイルを取り込む方法 #SnowflakeDB

さがらです。

SnowflakeはVARIANTと呼ばれる型のおかげで、JSONのような非構造データファイルでも簡単にテーブルデータのように取り扱うことが出来ます。

しかし、業務上少し取り込み時に問題が発生した形式のJSONファイルがありましたので、備忘録的に問題と対策を本エントリでまとめたいと思います。

発生した問題

発生した問題は、対象のファイル全体をarray[]で囲まれたJSONファイルをVARIANT型で取り込み後、上手くクエリができないというものでした。

具体的には、下記のようなデータですね。データの先頭と末尾に[]がある形式のJSONとなります。

[
  {
    "item_id": 1,
    "name": "ボールペン",
    "price": 100
  },
  {
    "item_id": 2,
    "name": "消しゴム",
    "price": 50
  },
  {
    "item_id": 3,
    "name": "筆箱",
    "price": 500
  }
]

これを何も気にせずデフォルト設定のJSONのファイルフォーマットでロードすると、下図のように[]もそのままにロードされます。

この状態でVARIANT型のデータに対してクエリを実行すると、下図のようにすべてNULLで表示されてしまいます。これではこのデータを使った分析等は何も出来ませんね…

解決策その１

さて、この問題に対する解決策は事前にクレンジング処理を入れるしかない…ということはありません。

解決策は、使用するファイルフォーマットで「外部配列を削除」にチェックを入れる。これだけでOKです！

このファイルフォーマットで同じJSONファイルを読み込むと、今度は先頭の[がなくなって、１レコードずつ分割されていることがわかるかと思います。

このテーブルに対して、先程と同じクエリを実行してみます。今度は狙い通りの結果を得られました！

ちなみに、コマンドでこのオプションを設定する場合はSTRIP_OUTER_ARRAYというオプションをTRUEにしてあげればOKです。

詳細は公式Docもご確認ください！

解決策その２

そうはいっても、「もう[]付きのJSONで数百GBのデータ入れてしまったよ…もう一度入れるのは辛いよ…」という方もいるのではないでしょうか。

そんな方向けに、LATERAL FLATTENを使った方法もご紹介します。

下図のように、FLATTEN関数でVARIANT型のカラムをフラット化したテーブルをLATERAL結合してあげればOKです。

「LATERAL FLATTENってなんぞ」という方は、下記の記事がとても詳しく書いてあるので、こちらも参考にしてみてください。

最後に

Snowflakeに蓄積したいJSONファイルが元のシステム等の仕様上、先頭と末尾に[]がついてしまうことは意外とあるものかと思います。

こういった仕様にも対応できる機能をデフォルトで持っているSnowflakeはいいなーと改めて感じた検証でした。笑